package com.springapp.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.log4j.Logger;

import com.sun.rowset.CachedRowSetImpl;
 
public class DataBase {

	private static String DBDRIVER = ""; // "com.mysql.jdbc.Driver";// 驱动类类名

	private static String DBURL = "";// "jdbc:mysql://192.168.0.32:3306/iiiis?useUnicode=true&amp;characterEncoding=utf-8";//
										// 连接URL

	private static String DBUSER = "";// "root";// 数据库用户名

	private static String DBPASSWORD = "";// "Vixuan724";// 数据库密码

	//public static Connection conn = null;

	//private static PreparedStatement ps = null;

	private static Logger logger = Logger.getLogger(DataBase.class);

	// private static ResultSet rs = null;

	/**
	 * 关于Statement优化 在动态SQL或有时间限制的命令中使用Statement对象
	 * 　　在执行SQL命令时，我们有二种选择：可以使用PreparedStatement对象，
	 * 也可以使用Statement对象。无论多少次地使用同一个SQL命令， PreparedStatement都只对它解析和编译一次。
	 * 当使用Statement对象时，每次执行一个SQL命令时，都会对它进行解析和编译。
	 * 这可能会使你认为，使用PreparedStatement对象比使用Statement对象的速度更快。
	 * 然而，我进行的测试表明，在客户端软件中，情况并非如此。
	 * 因此，在有时间限制的SQL操作中，除非成批地处理SQL命令，我们应当考虑使用Statement对象。
	 */ 
	static {
		try {
			// 此处只有通过获取文件路径方式 来得到配置文件
			String filePath = ConfigFileAddress.getConfigFilePath()
					+ "jdbc.properties";
			DBURL = HandleProperties.readValue(filePath, "url");
			DBUSER = HandleProperties.readValue(filePath, "user");
			DBPASSWORD = HandleProperties.readValue(filePath, "password");
			DBDRIVER = HandleProperties.readValue(filePath, "dbdriver");

		} catch (Exception e) {
			Debug.echo(e.toString());
			e.printStackTrace();
		}

	}

	// 获取数据库连接
	public synchronized static Connection getConnection() {
		Connection connection = null;
		try {

			Class.forName(DBDRIVER);// 注册驱动 
			connection = DriverManager.getConnection(DBURL, DBUSER, DBPASSWORD);// 获得连接对象

		} catch (ClassNotFoundException e) {// 捕获驱动类无法找到异常

			Debug.echo(e.toString());
			logger.error(e.toString());

		} catch (SQLException e) {// 捕获SQL异常

			Debug.echo(e.toString());
			logger.error(e.toString());
		}

		return connection;

	}

	// 查询数据
	public synchronized static ResultSet select(String sql) throws Exception {
		CachedRowSetImpl rowSet = new CachedRowSetImpl();
		
		Connection connection = null ;	// 数据连接
		PreparedStatement ps = null ;	// 执行查询
		ResultSet rs = null ;			// 结果集
		
		try {
			
			connection = getConnection();
			Debug.echo(sql);
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery(sql);
			
			rowSet.populate(rs);
			return rowSet;

		} catch (SQLException sqle) {

			throw new SQLException("select data Exception: "
					+ sqle.getMessage());

		} catch (Exception e) {

			throw new Exception("System error: " + e.getMessage());

		} finally{
			
			try {
				if(rs!=null)
					rs.close();
			} catch (Exception e2) {
				throw new Exception("rs close exception: " + e2.getMessage());
			}
			
			try {
				if (ps != null) 
					ps.close();
			} catch (Exception e) {
				throw new Exception("ps close exception: " + e.getMessage());
			}

			try {
				if (connection != null) 
					connection.close();
			} catch (Exception e) {
				throw new Exception("conn close exception: " + e.getMessage());
			}
			
		}

	}

	// 插入数据

	public static int insert(String sql) throws Exception {
		 
		int num = 0;// 计数
		Connection connection = null;
		PreparedStatement ps = null;
		try {
			Debug.echo("插入数据库语句:" + sql);
			connection = getConnection();
			ps = connection.prepareStatement(sql);
			num = ps.executeUpdate();

		} catch (SQLException sqle) {

			throw new SQLException("insert data Exception: "
					+ sqle.getMessage());

		} finally {

			try {

				if (ps != null) {

					ps.close();

				}

			} catch (Exception e) {

				throw new Exception("ps close exception: " + e.getMessage());

			}

			try {

				if (connection != null) {

					connection.close();

				}

			} catch (Exception e) {

				throw new Exception("conn close exception: " + e.getMessage());

			}

		}

		return num;

	}

	// 删除数据

	public static int delete(String sql) throws Exception {
		int num = 0;// 计数
		Connection connection = null;
		PreparedStatement ps = null;
		try {
			Debug.echo("插入数据库语句:" + sql);
			connection = getConnection();
			ps = connection.prepareStatement(sql);
			num = ps.executeUpdate();
		} catch (SQLException sqle) {
			throw new SQLException("delete data Exception: " + sqle.getMessage());
		} finally {

			try {

				if (ps != null) {

					ps.close();

				}

			} catch (Exception e) {

				throw new Exception("ps close Exception: " + e.getMessage());

			}

			try {

				if (connection != null) {

					connection.close();

				}

			} catch (Exception e) {

				throw new Exception("conn close Exception: " + e.getMessage());

			}

		}

		return num;

	}

	// 修改数据

	public static int update(String sql) throws Exception {
		int num = 0;// 计数
		Connection connection = null;
		PreparedStatement ps = null;
		try {
			Debug.echo("插入数据库语句:" + sql);
			connection= getConnection();
			ps = connection.prepareStatement(sql);
			num = ps.executeUpdate();
		} catch (SQLException sqle) {
			throw new SQLException("update data Exception: " + sqle.getMessage());
		} finally {

			try {

				if (ps != null) {

					ps.close();

				}

			} catch (Exception e) {

				throw new Exception("ps close Exception: " + e.getMessage());

			}

			try {

				if (connection != null) {

					connection.close();

				}

			} catch (Exception e) {

				throw new Exception("conn close Excepiton: " + e.getMessage());

			}

		}

		return num;

	}

	public static void sourceClose(ResultSet rs,Connection conn){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void closeAll(ResultSet rs){
		DataBase.sourceClose(rs, null);
	}
	
	// public common

	public static void main(String[] args) throws Exception {
		System.out.println(DataBase.getConnection());
	}

}